import pymysql
from pyecharts import options as opts
from pyecharts.charts import Line, Bar, Pie, Funnel
from pyecharts.globals import ThemeType
from pyecharts.charts import Map
from pyecharts import options as opts
from pyecharts.charts import Grid
from pyecharts.components import Table
from pyecharts.charts import WordCloud

# 1. 创建柱状图
def create_line_chart():
    db = pymysql.connect(host='localhost', user='root', password='QY521', db='data_shixun')
    #读取数据
    cursor = db.cursor()
    sql = "SELECT market_name, market_number FROM ads_market"
    cursor.execute(sql)
    market_data = cursor.fetchall()
    #绘制柱形图
    bar = (
        Bar(init_opts=opts.InitOpts(width="450px",height="370px",theme='west'))
        .add_xaxis([i[0] for i in market_data])
       .add_yaxis("市场数量", [i[1] for i in market_data])
        .set_global_opts(title_opts=opts.TitleOpts(title="活跃时间分布图"))
        .render("market_chart.html")
        )
    #关闭数据库连接
    db.close()


# 2. 创建饼图图
def create_bar_chart():
    db = pymysql.connect(host='localhost', user='root', password='QY521', db='data_shixun')
    # 读取数据
    cursor = db.cursor()
    sql = "SELECT sex, sex_number FROM ads_sex"
    cursor.execute(sql)
    sex_data = cursor.fetchall()
    #绘制饼图
    pie = (
        Pie(init_opts=opts.InitOpts(width="450px",height="370px",theme='west'))
        .add("", [list(i) for i in sex_data])
        .set_global_opts(title_opts=opts.TitleOpts(title="粉丝性别比例图"))
        .render("sex_chart.html")
        )
    #关闭数据库连接
    db.close()

# 3. 创建地图
def create_pie_chart():
    db = pymysql.connect(host='localhost', user='root', password='QY521', db='data_shixun')
    # 读取数据
    cursor = db.cursor()
    sql = "SELECT province_name, province_number FROM ads_province"
    cursor.execute(sql)
    result = cursor.fetchall()

    # 处理数据
    province_data = [(row[0], row[1]) for row in result]

    #绘制地图
    map = (
        Map(init_opts=opts.InitOpts(width="450px",height="370px",page_title="地域分布图",theme='west'))
        .add("地图", province_data, "china")
        .set_series_opts(
            label_opts=opts.LabelOpts(is_show=True, position="inside", font_size=10, font_weight="bold"),  # 显示每个地区的数据
            tooltip_opts=opts.TooltipOpts(trigger='item', formatter='{b}: {c}')  # 鼠标悬停提示格式
        )
        .set_global_opts(title_opts=opts.TitleOpts(title="粉丝地域分布图"),visualmap_opts=opts.VisualMapOpts(max_=max(row[1] for row in result)))
        .render("province_chart.html")
        )
    #关闭数据库连接
    db.close()
#4. 创建漏斗图
def create_funnel_chart():
    db = pymysql.connect(host='localhost', user='root', password='QY521', db='data_shixun')
    cursor = db.cursor()
    sql = "SELECT age_range, count FROM ads_age"
    cursor.execute(sql)
    ags_data = cursor.fetchall()

    funnel = (
        Funnel(init_opts=opts.InitOpts(width="450px", height="360px", theme='west'))
        .add("阶段转化率", [list(z) for z in ags_data], sort_='descending', gap=20, label_opts=opts.LabelOpts(position="inside"))
        .set_global_opts(title_opts=opts.TitleOpts(title="年龄段梯形图"))
        .set_series_opts(label_opts=opts.LabelOpts(is_show=True))
        .render("ags_chart.html")
    )
    db.close()

# 5. 创建表格
def create_table_chart():
    db = pymysql.connect(host='localhost', user='root', password='QY521', db='data_shixun')
    cursor = db.cursor()
    sql = "SELECT plays, comments, collections FROM ads_opu"
    cursor.execute(sql)
    opu_data = cursor.fetchall()

    headers = ["播放次数", "评论次数", "收藏次数"]  # 使用中文标题作为表头，也可以使用英文或其他语言
    rows = [[str(row[0]), str(row[1]), str(row[2])] for row in opu_data]  # 将数字转换为字符串

    table = (
        Table()
        .add(headers, rows)
        .set_global_opts(title_opts=opts.ComponentTitleOpts(title="总点赞、评论、收藏次数表"))
    )
    table.render("opu_table.html")
    db.close()



# 6. 创建词云图
def create_wordcloud_chart():
    db = pymysql.connect(host='localhost', user='root', password='QY521', db='data_shixun')
    cursor = db.cursor()
    sql = "SELECT times_name, times_number FROM ads_times"
    cursor.execute(sql)
    times_data = cursor.fetchall()

    wordcloud = (
        WordCloud(init_opts=opts.InitOpts(width="450px", height="370px", theme='west'))
        .add(series_name="", data_pair=[list(z) for z in times_data], word_size_range=[20, 100])
        .set_global_opts(title_opts=opts.TitleOpts(title="粉丝活跃时间段词云图"))
    )
    wordcloud.render("times_wordcloud.html")
    db.close()

# 7. 创建 HTML 页面来将图表嵌入到数据大屏中
def update_dashboard():
    html_content = '''
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Dashboard</title>
        <style>
            .container {
                display: flex;
                flex-wrap: wrap;
                gap: 20px;
            }
            .chart {
                flex: 1 1 calc(33% - 20px);
                box-sizing: border-box;
            }
        </style>
    </head>
    <body>
        <div class="container">
            <!-- 已有的图表 -->
            <div class="chart">
                <iframe src="market_chart.html" width="100%" height="400px" frameborder="0"></iframe>
            </div>
            <div class="chart">
                <iframe src="opu_table.html" width="100%" height="200px" frameborder="0"></iframe>
            </div>
            <div class="chart">
                <iframe src="sex_chart.html" width="100%" height="400px" frameborder="0"></iframe>
            </div>
            <!-- 新的图表 -->
            <div class="chart">
                <iframe src="ags_chart.html" width="100%" height="400px" frameborder="0"></iframe>
            </div>
            <div class="chart">
                <iframe src="province_chart.html" width="100%" height="400px" frameborder="0"></iframe>
            </div>
            <div class="chart">
                <iframe src="times_wordcloud.html" width="100%" height="400px" frameborder="0"></iframe>
            </div>
        </div>
    </body>
    </html>
    '''
    with open("dashboard.html", "w") as f:
        f.write(html_content)

# 主函数
def main():
    create_line_chart()  # 创建折线图
    create_bar_chart()   # 创建柱状图
    create_pie_chart()   # 创建饼图
    create_funnel_chart()  # 创建漏斗图
    create_table_chart()  # 创建表格
    create_wordcloud_chart()  # 创建词云图
    update_dashboard()  # 更新数据大屏

# 运行主函数
if __name__ == "__main__":
    main()


